/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.serc.pensieve.data;
import org.serc.pensieve.model.Inspection;
import org.serc.pensieve.model.UseCases;
import org.serc.pensieve.model.CheckList;
import java.lang.String;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Shani
 */
public class PreparationDao
{

    public ArrayList<Inspection> GetNewInspectionData(int tempId) throws SQLException
    {
        ArrayList<Inspection> tempInspection = new ArrayList<Inspection>();
      //  Inspection tempInsp = new Inspection();
       
        try {
            ResultSet res;
            DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "select * from newinspection where InspectorID = "+tempId;
            System.out.println(myQuery);
            res = stat.executeQuery(myQuery);
            int newInspectionId;
            int newInspectorId;
            int newSRSId;
            int newCheckListId;
            String srsName;
            while (res.next()) {
      // tempInspection.add(null)
                Inspection tempInsp = new Inspection();
                tempInsp.inspectionNum = res.getInt("ID");
                tempInsp.SRSID = res.getInt("SRSID");
                tempInsp.checkListID = res.getInt("CheckListID");
                // inspection id has id of created inspection by admin.
                tempInsp.inspectionID = res.getInt("InspectionID");
                tempInsp.inspectionName = res.getString("InspectionName");
                tempInsp.inspectionDescription = res.getString("InspectionDescription");

                System.out.println("Id is :" );
                //Adding Inspection instance to list.
                tempInspection.add(tempInsp);
            }
          }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
        //array list
 return tempInspection;

        //throw new UnsupportedOperationException("Not yet implemented");


    }

    public ArrayList<UseCases> GetUseCases(int srsId) {
       // throw new UnsupportedOperationException("Not yet implemented");

          ArrayList<UseCases> tempUseCases = new  ArrayList<UseCases>();
           try {
            ResultSet res;
            DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "select * from usecases where SRSID = "+srsId;
            System.out.println(myQuery);
            res = stat.executeQuery(myQuery);
            String srsName;
            while (res.next()) {
      // tempInspection.add(null)
                UseCases tempUsecase = new UseCases();
                tempUsecase.ID =res.getInt("ID");
                tempUsecase.SRSID = res.getInt("SRSID");
                tempUsecase.actors = res.getString("Actors");
                tempUsecase.alternativeFlow = res.getString("AlternativeFlow");
                tempUsecase.mainFlow = res.getString("Mainflow");
                tempUsecase.postConditions = res.getString("PostCondition");
                tempUsecase.preConditions = res.getString("PreCOndition");
                tempUsecase.useCaseDescription = res.getString("Description");
                
                System.out.println("Id is :" );
                //Adding Inspection instance to list.
                tempUseCases.add(tempUsecase);
            }
          }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
       
          return tempUseCases;
    }

    public ArrayList<CheckList> GetCheckList(int srsId) {
      //  throw new UnsupportedOperationException("Not yet implemented");

          ArrayList<CheckList> tempUseCases = new  ArrayList<CheckList>();
           try {
            ResultSet res;
            DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "select * from checklistdata where CheckListID ="+srsId;
            System.out.println(myQuery);
            res = stat.executeQuery(myQuery);
            String srsName;
            while (res.next()) {
      // tempInspection.add(null)
                CheckList tempCheckList = new CheckList();
                tempCheckList.ID =res.getInt("ID");
                tempCheckList.elemData = res.getString("Description");
                tempCheckList.elemHeading = res.getString("Heading");
               
                System.out.println("Id is :" );
                //Adding Inspection instance to list.
                tempUseCases.add(tempCheckList);
            }
          }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }




 return tempUseCases;


    }
    //function ended.

    public boolean SavingState(int inspectionId,int usecaseID,int srsId,String errorDes,String passFailval) throws SQLException
    {
int res=0;

// deciding update or delete?







int aa11= 0;
 try {
            //ResultSet res;
ResultSet upOrIns;
 
     DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "select * from errorsanddetails where UseCaseID = "+usecaseID+" AND SRSID = "+srsId +" AND InspectionID ="+inspectionId;
            System.out.println(myQuery);
            upOrIns = stat.executeQuery(myQuery);
        while(upOrIns.next())
        {
            aa11=1;
        }
            String srsName;
   }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }


if(aa11==0)
{





//---------
        //Query to insert data
         try {
            DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "insert into errorsanddetails (UseCaseID,ErrorDescription,PassFailStatus,SRSID,InspectionID) values("+usecaseID+",'"+errorDes+"','"+passFailval+"',"+srsId+","+inspectionId+")";
            System.out.println(myQuery);
            res = stat.executeUpdate(myQuery);
            String srsName="jg";
         }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }



        if(res==1)
        {
             return true;
        }
        else
        {
            return false;

        }
}
else
{
int res2 = 0;
    //Update query if already exist this use case insoection.
     try {
            DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
       String myQuery = "update errorsanddetails SET UseCaseID = "+usecaseID+"  ,ErrorDescription = '"+errorDes+"' , PassFailStatus ='"+passFailval+"' where UseCaseID = "+usecaseID+" AND SRSID = "+srsId +" AND InspectionID ="+inspectionId;
            System.out.println(myQuery);
            res2 = stat.executeUpdate(myQuery);
            String srsName="jg";
         }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
        if(res2==1)
        {
             return true;
        }
        else
        {
            return false;

        }



}

}

    public int SaveErrosSnippets(String tempSRSID, String tempUsecase, String tempInspectionId, String selectedText, String textOfSnippet) {
       // throw new UnsupportedOperationException("Not yet implemented");
int srsid = Integer.parseInt(tempSRSID);
int usecaseid = Integer.parseInt(tempUsecase);
int inspectionid = Integer.parseInt(tempInspectionId);
int res =0;
//Query to insert data
         try {
            DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "insert into smallerrors (KeyString,ValueString,SRSID,UseCaseID,InspectionID) values('"+selectedText+"','"+textOfSnippet+"',"+srsid+","+usecaseid+","+inspectionid+")";
            System.out.println(myQuery);
            res = stat.executeUpdate(myQuery);
            String srsName="jg";
         }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }



        if(res==1)
        {
             return 1;
        }
        else
        {
            return 0;

        }

        
  //  return 1;
    }

    public int createMeetings(String tempSRSID, String tempUsecase, String tempInspectionId) {
        //
        
        
        
int srsid = Integer.parseInt(tempSRSID);
int usecaseid = Integer.parseInt(tempUsecase);
int inspectionid = Integer.parseInt(tempInspectionId);
        
      int moderator=0;  
 int aa11 =0;       
 try {
            //ResultSet res;
ResultSet upOrIns;
 
     DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "select * from newinspection where InspectionID = "+inspectionid;
            System.out.println(myQuery);
            upOrIns = stat.executeQuery(myQuery);
        while(upOrIns.next())
        {
            aa11=1;
            moderator  =upOrIns.getInt("Moderator_ID");
        
        }
            String srsName;
   }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }


if(aa11==0)
{
}       
        
        
        
        
        
        
        
        
        
int res =0;
//Query to insert data
         try {
            DAO d = new DAO();
            Connection conn = d.makeConn();
            Statement stat = (Statement) conn.createStatement();
            String myQuery = "insert into newmeetings (SRSID,UsecaseID,InspectionID,moderatorID) values("+srsid+","+usecaseid+","+inspectionid+","+moderator+")";
            System.out.println(myQuery);
            res = stat.executeUpdate(myQuery);
            String srsName="jg";
         }
          catch (ClassNotFoundException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }
          catch (SQLException ex)
          {
            Logger.getLogger(AdminDao.class.getName()).log(Level.SEVERE, null, ex);
          }



        if(res==1)
        {
             return 1;
        }
        else
        {
            return 0;

        }

    }



}
